from typing import Optional, List, Tuple
from sqlalchemy.orm import Session
from app.models.iaro_career.ite_detail import ITEDetail
from app.models.iaro_career.scholars import Scholar
from app.models.iaro_career.scholar_status import ScholarStatus
from app.models.iaro_coop_admin.sponsor import Sponsor, SponsorGroup, SponsorGroupList

class SponsorsReportRepo:
    def __init__(self, db: Session):
        self.db = db

    def check_cocode_exists(self, cocode: str) -> bool:
        exists = (
            self.db.query(ITEDetail)
            .filter(ITEDetail.cocode == cocode)
            .first()
        )
        return exists is not None
    
    def get_cocodes_from_query(self, query: str) -> Tuple[List[str], bool]:

        group = (
            self.db.query(SponsorGroupList)
            .filter(SponsorGroupList.sponsor_group_code == query)
            .first()
        )
        if group:
            cocode_rows = (
                self.db.query(Sponsor.sponsor_code)
                .join(SponsorGroup, Sponsor.sponsor_id == SponsorGroup.sponsor_id)
                .filter(SponsorGroup.sponsor_group_id == group.sponsor_group_id)
                .all()
            )

            return [r.sponsor_code for r in cocode_rows], True
        else:
            return [query], False 

    def get_commitment_report_records(self, cocodes: Optional[List[str]] = None) -> List[dict]:
        q = (
            self.db.query(
                ITEDetail.cocode.label("COCODE"),
                Scholar.program_id.label("PROGRAM_ID"),
                ITEDetail.ite_number.label("ITE_NUMBER"),
                Scholar.first_name.label("FIRST_NAME"),
                Scholar.last_name.label("LAST_NAME"),
                ScholarStatus.status_description.label("STUDENT_TYPE"),
            )
            .join(Scholar, ITEDetail.scholar_id == Scholar.scholar_id)
            .join(ScholarStatus, Scholar.status_id == ScholarStatus.status_id)
        )
        if cocodes:
            q = q.filter(ITEDetail.cocode.in_(cocodes))
        results = q.all()
        return [
            {
                "COCODE": r.COCODE,
                "PROGRAM ID": r.PROGRAM_ID,
                "ITE NUMBER": r.ITE_NUMBER,
                "FIRST NAME": r.FIRST_NAME,
                "LAST NAME": r.LAST_NAME,
                "STUDENT TYPE": r.STUDENT_TYPE,
            }
            for r in results 
        ]